import plotly.express as px
from postgresql_tools import load_query_df, create_db_conn
# Create connection to PostgreSQL database
conn = create_db_conn('config.ini')
# Load the member versus casual data into a dataframe
df_members = load_query_df('./sql/member_casual_volume.sql', conn, ['month_year', 'member_casual', 'trip_count'])
# Create a line chart
fig = px.line(df_members, x='month_year', y='trip_count', color='member_casual')
# Show the line chart in the Jupyter notebook
fig.show()
# Get rides by day of the week
df_members_dow = load_query_df('./sql/member_casual_volume_dow.sql', conn, ['day_of_week',
'member_casual',
'total_trips'])
day_of_week_names = {0: 'Sunday', 1: 'Monday', 2: 'Tuesday',
3: 'Wednesday', 4: 'Thursday', 5: 'Friday', 6: 'Saturday'}
df_members_dow['day_of_week_name'] = df_members_dow['day_of_week'].map(day_of_week_names)
fig = px.bar(df_members_dow,
x='day_of_week_name',
y='total_trips',
color='member_casual',
facet_col='member_casual')
fig.update_xaxes(title='Day of the week')
fig.show()
df_members_dow_duration = load_query_df('./sql/member_casual_duration.sql', conn, ['day_of_week',
'member_casual',
'average_duration'])
df_members_dow_duration['day_of_week_name'] = df_members_dow_duration['day_of_week'].map(day_of_week_names)
df_members_dow_duration['average_duration_minutes'] = df_members_dow_duration['average_duration'] / 60
fig = px.bar(df_members_dow_duration,
x='day_of_week_name',
y='average_duration_minutes',
color='member_casual',
facet_col='member_casual',
category_orders={'member_casual': list(df_members_dow_duration['member_casual'].unique())})
fig.update_xaxes(title='Day of the week')
fig.show()
df_members_dow_total_duration = load_query_df('./sql/member_casual_dow_duration.sql',
conn, ['day_of_week',
'member_casual',
'total_duration'])
df_members_dow_total_duration['day_of_week_name'] = df_members_dow_total_duration['day_of_week'].map(day_of_week_names)
df_members_dow_total_duration['total_duration_days'] = df_members_dow_total_duration['total_duration'] / 86400
fig = px.bar(df_members_dow_total_duration,
x='day_of_week_name',
y='total_duration_days',
color='member_casual',
facet_col='member_casual',
category_orders={'member_casual': list(df_members_dow_total_duration['member_casual'].unique())})
fig.update_xaxes(title='Day of the week')
fig.show()
df_members_at_risk_trips = load_query_df('./sql/at_risk_trips.sql',
conn,
['day_of_week','member_casual','at_risk_trips'])
df_members_at_risk_trips['day_of_week_name'] = df_members_at_risk_trips['day_of_week'].map(day_of_week_names)
fig = px.bar(df_members_at_risk_trips,
x='day_of_week_name',
y='at_risk_trips',
color='member_casual',
facet_col='member_casual',
category_orders={'member_casual': list(df_members_at_risk_trips['member_casual'].unique())})
fig.update_xaxes(title='Day of the week')
fig.show()
# Load the heatmap data into a dataframe
df_trips = load_query_df('./sql/heatmap_casual.sql', conn, ['start_date',
'latitude',
'longitude',
'trip_count'])
# Create the heat map to show where to place ads for memberships
fig = px.density_mapbox(df_trips, lat='latitude', lon='longitude', z='trip_count',
radius=10,
center=dict(lat=41.9, lon=-87.6), zoom=10,
mapbox_style='open-street-map', animation_frame=df_trips['start_date'],
height=800)
# Show the heat map in the Jupyter notebook
fig.show()
conn.close()